package com.agriculture.mapper;

import com.agriculture.domain.User;
import com.agriculture.domain.Product;
import org.apache.ibatis.annotations.*;
import java.util.List;

@Mapper
public interface UserMapper {
    @Insert("INSERT INTO Users(username, password, full_name, role, phone_number, email, address, status) " +
           "VALUES(#{username}, #{password}, #{fullName}, #{role}, #{phoneNumber}, #{email}, #{address}, #{status})")
    @Options(useGeneratedKeys = true, keyProperty = "userId")
    int insert(User user);

    @Select("SELECT * FROM Users WHERE user_id = #{userId}")
    User findById(Long userId);

    @Select("SELECT * FROM users WHERE username = #{username}")
    @Results({
        @Result(property = "userId", column = "user_id"),
        @Result(property = "username", column = "username"),
        @Result(property = "password", column = "password"),
        @Result(property = "fullName", column = "full_name"),
        @Result(property = "role", column = "role"),
        @Result(property = "phoneNumber", column = "phone_number"),
        @Result(property = "email", column = "email"),
        @Result(property = "address", column = "address"),
        @Result(property = "status", column = "status"),
        @Result(property = "createdAt", column = "created_at"),
        @Result(property = "updateTime", column = "update_time"),
        @Result(property = "lastLoginTime", column = "last_login_time")
    })
    User findByUsername(@Param("username") String username);

    @Update("UPDATE Users SET " +
            "phone_number = #{phoneNumber}, " +
            "email = #{email}, " +
            "address = #{address}, " +
            "update_time = NOW() " +
            "WHERE user_id = #{userId}")
    int update(User user);

    @Update("UPDATE Users SET last_login_time=NOW() WHERE user_id=#{userId}")
    int updateLoginTime(Long userId);

    @Select("SELECT * FROM Users WHERE role = #{role}")
    List<User> findByRole(String role);

    @Select({
        "<script>",
        "SELECT user_id, username, role, phone_number, email, address, status",
        "FROM users",
        "WHERE role != 'admin'",
        "<if test='keyword != null and keyword != \"\"'>",
        "  AND (username LIKE CONCAT('%', #{keyword}, '%')",
        "       OR email LIKE CONCAT('%', #{keyword}, '%'))",
        "</if>",
        "<if test='role != null and role != \"\"'>",
        "  AND role = #{role}",
        "</if>",
        "LIMIT #{offset}, #{pageSize}",
        "</script>"
    })
    @Results({
        @Result(property = "userId", column = "user_id"),
        @Result(property = "username", column = "username"),
        @Result(property = "role", column = "role"),
        @Result(property = "phoneNumber", column = "phone_number"),
        @Result(property = "email", column = "email"),
        @Result(property = "address", column = "address"),
        @Result(property = "status", column = "status")
    })
    List<User> findUsers(String keyword, String role, int offset, int pageSize);

    @Select({
        "<script>",
        "SELECT COUNT(*) FROM users",
        "WHERE role != 'admin'",
        "<if test='keyword != null and keyword != \"\"'>",
        "  AND (username LIKE CONCAT('%', #{keyword}, '%')",
        "       OR email LIKE CONCAT('%', #{keyword}, '%'))",
        "</if>",
        "<if test='role != null and role != \"\"'>",
        "  AND role = #{role}",
        "</if>",
        "</script>"
    })
    int countUsers(String keyword, String role);

    @Update("UPDATE users SET status = #{status} WHERE user_id = #{userId}")
    void updateStatus(Long userId, String status);

    @Delete("DELETE FROM users WHERE user_id = #{userId}")
    void deleteById(Long userId);

    @Select("SELECT * FROM Users WHERE user_id = #{userId}")
    User getUser(Long userId);

    @Select("SELECT COUNT(*) FROM Users WHERE phone_number = #{phoneNumber} AND user_id != #{userId}")
    int checkPhoneNumberExists(String phoneNumber, Long userId);

    @Select("SELECT * FROM Users WHERE role = #{role}")
    @Results({
        @Result(property = "userId", column = "user_id"),
        @Result(property = "username", column = "username"),
        @Result(property = "password", column = "password"),
        @Result(property = "fullName", column = "full_name"),
        @Result(property = "role", column = "role"),
        @Result(property = "phoneNumber", column = "phone_number"),
        @Result(property = "email", column = "email"),
        @Result(property = "address", column = "address"),
        @Result(property = "status", column = "status"),
        @Result(property = "createdAt", column = "created_at"),
        @Result(property = "updateTime", column = "update_time"),
        @Result(property = "lastLoginTime", column = "last_login_time")
    })
    List<User> selectByRole(@Param("role") String role);

    @Select({
        "<script>",
        "SELECT p.*, u.full_name as supplierName, u.phone_number as supplierPhone, u.address as supplierAddress",
        "FROM products p",
        "LEFT JOIN users u ON p.owner_id = u.user_id",
        "WHERE p.status = 'on_sale'",
        "<if test='search != null and search != \"\"'>",
        "  AND (p.product_name LIKE CONCAT('%', #{search}, '%')",
        "       OR p.product_id LIKE CONCAT('%', #{search}, '%'))",
        "</if>",
        "<if test='category != null and category != \"\"'>",
        "  AND p.category = #{category}",
        "</if>",
        "<if test='supplier != null and supplier != \"\"'>",
        "  AND u.full_name LIKE CONCAT('%', #{supplier}, '%')",
        "</if>",
        "ORDER BY p.created_at DESC",
        "LIMIT #{offset}, #{pageSize}",
        "</script>"
    })
    @Results({
        @Result(property = "productId", column = "product_id"),
        @Result(property = "productName", column = "product_name"),
        @Result(property = "imageUrl", column = "image_url"),
        @Result(property = "createdAt", column = "created_at"),
        @Result(property = "updateTime", column = "update_time"),
        @Result(property = "harvestDate", column = "harvest_date"),
        @Result(property = "ownerId", column = "owner_id"),
        @Result(property = "ownerType", column = "owner_type")
    })
    List<Product> getPurchaseProducts(@Param("search") String search, 
                                    @Param("category") String category,
                                    @Param("supplier") String supplier,
                                    @Param("offset") int offset,
                                    @Param("pageSize") int pageSize);

    @Select("SELECT p.*, u.full_name as supplierName, u.phone_number as supplierPhone, u.address as supplierAddress " +
           "FROM products p " +
           "LEFT JOIN users u ON p.owner_id = u.user_id " +
           "WHERE p.product_id = #{productId}")
    @Results({
        @Result(property = "productId", column = "product_id"),
        @Result(property = "productName", column = "product_name"),
        @Result(property = "imageUrl", column = "image_url"),
        @Result(property = "createdAt", column = "created_at"),
        @Result(property = "updateTime", column = "update_time"),
        @Result(property = "harvestDate", column = "harvest_date"),
        @Result(property = "ownerId", column = "owner_id"),
        @Result(property = "ownerType", column = "owner_type")
    })
    Product getProductDetail(@Param("productId") Long productId);

    @Select({
        "<script>",
        "SELECT COUNT(*) FROM products p",
        "LEFT JOIN users u ON p.owner_id = u.user_id",
        "WHERE p.status = 'on_sale'",
        "<if test='search != null and search != \"\"'>",
        "  AND (p.product_name LIKE CONCAT('%', #{search}, '%')",
        "       OR p.product_id LIKE CONCAT('%', #{search}, '%'))",
        "</if>",
        "<if test='category != null and category != \"\"'>",
        "  AND p.category = #{category}",
        "</if>",
        "<if test='supplier != null and supplier != \"\"'>",
        "  AND u.full_name LIKE CONCAT('%', #{supplier}, '%')",
        "</if>",
        "</script>"
    })
    int countPurchaseProducts(@Param("search") String search,
                            @Param("category") String category,
                            @Param("supplier") String supplier);

    @Select("SELECT * FROM Users WHERE user_id = #{userId}")
    User selectById(Long userId);
} 